﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Drawing.Text;
//using System.Windows;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using SpectationClient;
using SpectationClient.SQLCommandBuilder;
using SpectationClient.Stuff;

using System.IO;
using SpectationClient.DataBaseDescription;
using SpectationClient.Async;

using Npgsql;

namespace SpectationClient.GUI {
    public partial class SubShowSelectionTable : Form {
        DBManager dbm = null;
        NpgsqlCommand cmd = null;
        DataTable dt = null;
        TableInfo ti = null;

        private ErrorList EL;
        public event EventHandler ResultOK_hasSelection;
        private AGetDatabaseObject AGDO;
        const String C_TSL_STATUS_DEFAULT = "Warte auf Benutzereingabe";
        private bool allowMultipleSelection;
     
        private int STATUS_DOTS = 0;
        private int STATUS_DOTS_MAX = 3;

        private StringBuilder SB = new StringBuilder();
        private const String CDT_VALUEMEMBER = "VALUEMEMBER";
        private const String CDT_DISPLAYMEMBER = "DISPLAYMEMBER";
        private const String CDT_LIKE = "DC_LIKE";
        private const String CDT_CNAME = "COLUMNNAME";
        private const String CDT_TYPE = "TYPE";
        
        /// <summary>
        /// id_column_names = names of the ids - columns
        /// displayvalue = //TODO
        /// </summary>
        /*
        public struct DISPLAY_VALUES {
            public List<String> id_colum_names;
            public List<List<DB_ID>> ids;
            public List<String> displayvalues; 
        }*/

        /// <summary>
        /// Struct to contain an array with the names of DB columns and a List of ID values. Each List item has n spectraIDs, since
        /// n columns might be the primary key.
        /// </summary>
        /*
        public struct ID_VALUES {
            public String[] id_column_names;
            public List<DB_ID[]> spectraIDs;
        }
        */
        public SubShowSelectionTable(ref DBManager dbm, String schema, String table, bool allowMultipleSeclections, bool use_emptyrow) {
            InitializeComponent();

            this.Text = String.Format("Datenbanktabelle {0}.{1}", schema, table);
            initialize(ref dbm, schema, table, allowMultipleSeclections, use_emptyrow);
        }

        public SubShowSelectionTable(ref DBManager dbm, String schema, String table, bool allowMultipleSeclections, bool use_emptyrow, string title_text, string ok_button_text) {
            InitializeComponent();

            this.Text = title_text;
            this.btOK.Text = ok_button_text;
            initialize(ref dbm, schema, table, allowMultipleSeclections, use_emptyrow);
        }

        private void initialize(ref DBManager dbm, String schema, String table
                       , bool allowMultipleSelections, bool use_emptyrow) {
            this.TIMER.Tick += new EventHandler(TIMER_Tick);

            this.Icon = Resources.Icon;
            this.EL = new ErrorList();
            this.EL.ErrorAdded += new EventHandler(EL_ErrorAdded);
            this.EL.ErrorListIsEmpty += new EventHandler(EL_ErrorListIsEmpty);
            this.dbm = dbm;
            this.ti = dbm.getTableInfo(schema, table);
         
            this.AGDO = new AGetDatabaseObject();
            this.AGDO.GetDataTableCompleted +=new GetDataTableCompletedEventHandler(AGDO_GetDataTableCompleted);
            this.FormClosing += new FormClosingEventHandler(SubShowSelectionTable_FormClosing);
            
            this.allowMultipleSelection = allowMultipleSelections;
            this.tsl_status.Text = C_TSL_STATUS_DEFAULT;
            
            DGV.MultiSelect = allowMultipleSelections;
            this.DGV.SelectionChanged += new EventHandler(DGV_SelectionChanged);
            DGV_Constraints.CellValueChanged += new DataGridViewCellEventHandler(DGV_Const_Text_CellValueChanged);
            initConstraints();
            this.tsTBMax.Text = Properties.Settings.Default.DEF_ROWLIMIT.ToString();
            this.tsTBOFFSET.Text = Properties.Settings.Default.DEF_ROWOFFSET.ToString();
            getDataTable();        
        }

        void AGDO_GetDataTableCompleted(object sender, GetDataTableCompletedEventArgs e) {
            if(e.Cancelled) {

            } else if(e.Error != null) {
                //this.SQL_INIT_CORRECT = false;
                this.RTB.Text += TextHelper.Exception2String(e.Error);
            } else {
                this.dt = e.DataTable;
                FormHelper.initDGVColumns(DGV, this.dt, this.ti);
                this.Cursor = Cursors.Default;
                bt_SearchNew.Enabled = true;
                btOK.Enabled = true;
                tsb_back.Enabled = true;
                tsb_forward.Enabled = true;
                this.tsl_status.Text = "Tabelle geladen";
            }
           
        }

        void SubShowSelectionTable_FormClosing(object sender, FormClosingEventArgs e) {
            AGDO.CancelAllRequest();
        }

        void TIMER_Tick(object sender, EventArgs e) {
            if(STATUS_DOTS == STATUS_DOTS_MAX){
                this.tsl_status.Text = this.tsl_status.Text.TrimEnd(new char[]{'.'});
                STATUS_DOTS = 0;
            }else{
                this.tsl_status.Text += '.';
                STATUS_DOTS++;
            }
        }

        void dbm_InfoStreamEvent(string info) {
            this.tsl_status.Text = info;
        }
        public TableInfo TableInfo {
            get { return this.ti; }
        }
        private void EL_ErrorListIsEmpty(object sender, EventArgs e) {
            this.prepareCommand();
        }

        private void EL_ErrorAdded(object sender, EventArgs e) {
            this.RTB.Text = "Bitte Angaben überprüfen!";
        }

        private void DGV_Const_Text_CellValueChanged(object sender, DataGridViewCellEventArgs e) {
            if (e.ColumnIndex == DGV_Constraints.Columns[CDT_LIKE].Index) {
                DataGridViewTextBoxCell VC = (DataGridViewTextBoxCell) DGV_Constraints[e.ColumnIndex, e.RowIndex];
                DataGridViewTextBoxCell TC = (DataGridViewTextBoxCell)DGV_Constraints[CDT_TYPE, e.RowIndex];
                //check number-columns only
                if (VC.Value == null) {   
                    EL.removeError(VC);
                    EVENT_PrepareCommand(null, null);

                } else if(VC.Value.ToString() == ""){
                    VC.Value = null;
                    EL.removeError(VC);
                    EVENT_PrepareCommand(null, null);
                
                } else if (DataHelper.isSQL_Number((Type)TC.Value)) {
                    String valueString = VC.Value.ToString().Trim();
                    if(valueString.Length > 0) {
                        Double[] vals = TextHelper.parseDoubles(VC.Value.ToString());
                        if(vals.Length == 0) {
                            EL.addError(VC, "Bitte Zahl angeben");
                        } else {
                            EL.removeError(VC);
                            EVENT_PrepareCommand(null, null);
                        }

                    } else {
                        EL.removeError(VC);
                    }

                 

                } else if (DataHelper.isSQL_Text((Type)TC.Value)) {
                    EVENT_PrepareCommand(null, null);
                }
            }
        }

        
       


     
        private void initConstraints2(DataGridView dgv_constraints) {
            //Dictionary<ColumnInfo, Constraint>
            DataGridViewTextBoxColumn tb_dbcol = new DataGridViewTextBoxColumn();
            tb_dbcol.ReadOnly = true;
            tb_dbcol.HeaderText = "Spalte";
            tb_dbcol.Name = CDT_CNAME;
            DataGridViewTextBoxColumn tb_db_LIKE = new DataGridViewTextBoxColumn();
            tb_db_LIKE.HeaderText = "Werte";
            tb_db_LIKE.ToolTipText = "Mögliche Werte dieser Spalte";
            tb_db_LIKE.Name = CDT_LIKE;
            DataGridViewTextBoxColumn tb_coltype = new DataGridViewTextBoxColumn();
            tb_coltype.Visible = false;
            tb_coltype.Name = CDT_TYPE;
            tb_coltype.ValueType = typeof(Type);
            tb_coltype.HeaderText = "Datentyp";

            dgv_constraints.Columns.Add(tb_dbcol);
            dgv_constraints.Columns.Add(tb_db_LIKE);
            dgv_constraints.Columns.Add(tb_coltype);

            //Text Constraints
            foreach(ColumnInfo ci in this.ti.Values) {
                if(DataHelper.isSQL_Number(ci.ValueType) ||
                    DataHelper.isSQL_Text(ci.ValueType)) {
                    DGV_Constraints.Rows.Add(new Object[] { ci.Name, DBNull.Value, ci.ValueType });
                }
            }
        }

        private void initConstraints() {
            DataGridViewTextBoxColumn tb_dbcol = new DataGridViewTextBoxColumn();
            tb_dbcol.ReadOnly = true;
            tb_dbcol.HeaderText = "Spalte";
            tb_dbcol.Name = CDT_CNAME;
            DataGridViewTextBoxColumn tb_db_LIKE = new DataGridViewTextBoxColumn();
            tb_db_LIKE.HeaderText = "Textanfang";
            tb_db_LIKE.ToolTipText = "Anfang der Textwerte in dieser Spalte";
            tb_db_LIKE.Name = CDT_LIKE;
            DataGridViewTextBoxColumn tb_coltype = new DataGridViewTextBoxColumn();
            tb_coltype.Visible = false;
            tb_coltype.Name = CDT_TYPE;
            tb_coltype.ValueType = typeof(Type);
            tb_coltype.HeaderText = "Datentyp";

            DGV_Constraints.Columns.Add(tb_dbcol);
            DGV_Constraints.Columns.Add(tb_db_LIKE);
            DGV_Constraints.Columns.Add(tb_coltype);

            //Text Constraints
            foreach (ColumnInfo ci in this.ti.Values) {
                if (DataHelper.isSQL_Number(ci.ValueType) ||
                    DataHelper.isSQL_Text(ci.ValueType)){
                    DGV_Constraints.Rows.Add(new Object[] {ci.Name, DBNull.Value, ci.ValueType});
                }
            }
        
        }

        private void EVENT_PrepareCommand(object sender, EventArgs e) {
            if (EL.isEmpty) {
                prepareCommand();
            }
        }
        private void prepareCommand() {
            
            ConditionList cl = new ConditionList(ConditionList.Op.AND);
            foreach (DataGridViewRow r in DGV_Constraints.Rows) {
                if (!r.IsNewRow &&
                    r.Cells[CDT_LIKE].Value != null &&
                    r.Cells[CDT_LIKE].Value != DBNull.Value &&
                    ((String)r.Cells[CDT_LIKE].Value).Length > 0) {

                    String columnname = (String)r.Cells[CDT_CNAME].Value;
                    object columnvalue = r.Cells[CDT_LIKE].Value;
                    Type coltype = ti[columnname].ValueType;

                    if(DataHelper.isSQL_Number(coltype)) {
                        String values = columnvalue.ToString();
                        Double[] vals = TextHelper.parseDoubles(values);
                        if(vals.Length > 0) {
                            Condition c = new Condition(columnname, Condition.Op.EQ);
                            foreach(Double v in vals) {
                                c.Add(v);
                            }
                            cl.Add(c);
                        }
                        
                    } else if(DataHelper.isSQL_Text(coltype)) {
                        Condition.Op cop = Condition.getStringComparer(cbExactMatch.Checked, cbCaseSensitive.Checked);
                        cl.Add(new Condition(columnname, cop, columnvalue));
                    } else {
                        throw new Exception("SubShowSelectionTable.prepareCommand(): use of " + coltype.Name + " is not implemented!");
                    }        
                }
            }
            
            if (tbSearchALL.Text.Length > 0) {
                NpgsqlCommand c = SQLCommandBuilder.CommandBuilder.getStringSearch(ti, tbSearchALL.Text, cbExactMatch.Checked, cbCaseSensitive.Checked);
                cl.Add(c);
            }
            cmd = SQLCommandBuilder.CommandBuilder.getSELECT(ti, true);
            //cmdTableOIDs = new NpgsqlCommand(String.Format("SELECT * FROM {0}", RecentTableInfo.SchemaTableName));
            if (!cl.IsEmpty) {
                cmd.CommandText += " WHERE ";
                SQLCommandBuilder.CommandBuilder.appendCmd(ref cmd, cl.getCmd());
            }
            cmd.CommandText += String.Format(" LIMIT {0} OFFSET {1}", Int16.Parse(tsTBMax.Text), Int16.Parse(tsTBOFFSET.Text));
            cmd.Connection = dbm.Connection;
            this.RTB.Text = SQLCommandBuilder.CommandBuilder.CommandToString(cmd);
   
        }

        private void getDataTable() {
            try {
                prepareCommand();
                tsl_status.Text = "Datenbankanfrage";
                this.TIMER.Start();
                this.Cursor = Cursors.WaitCursor;
                AGDO.GetDataTableAsync(this.cmd);
                bt_SearchNew.Enabled = false;
                btOK.Enabled = false;
                tsb_back.Enabled = false;
                tsb_forward.Enabled = false;
            } catch (Exception ex) {
                MessageBox.Show(TextHelper.Exception2String(ex), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                this.Cursor = Cursors.Default;
            }
        }

        public List<Object> getSelectedColumnValues(String column) {
            List<Object> list = new List<object>();
            foreach(DataGridViewRow r in DGV.SelectedRows) {
                list.Add(r.Cells[column].Value);
            }
            return list;
        }

        public List<Object[]> getSelectedValues() {
            return this.getSelectedValues(this.ti.Keys.ToArray());
        }

        public List<Object[]> getSelectedValues(String[] columns) {
            List<Object[]> l = new List<Object[]>();

            foreach(DataGridViewRow r in DGV.SelectedRows) {
                Object[] o = new Object[columns.Count()];
                for(int i=0; i<columns.Count(); i++) {
                    o[i] = r.Cells[columns[i]].Value;
                }
                l.Add(o);
            }
            return l;
        }

        public List<Object[]> getSelectedIDs() {
            if(this.ti == null) return null;

            return this.getSelectedValues(this.ti.PrimaryKey.Names.ToArray());
        }

        public DataTable getSelectedRows(){
            DataTable dt_new = this.dt.Clone();
            foreach (DataGridViewRow r in DGV.SelectedRows) {
                DataRow nr = dt_new.NewRow();
                foreach (DataColumn c in dt_new.Columns) {
                    nr[c] = r.Cells[c.ColumnName].Value;
                }
                dt_new.Rows.Add(nr);
            }
            return dt_new;
        }

        private void DGV_SelectionChanged(object sender, EventArgs e) {
            if (DGV.SelectedRows.Count <= 0) {
                btOK.Enabled = false;
            }else{
                btOK.Enabled = true;
            }
        }



        private void tsTBMax_TextChanged(object sender, EventArgs e) {
            Int16 num;
            if (!Int16.TryParse(tsTBMax.Text, out num) || num < 0) {
                EL.addError(tsTBMax, "Benötigt eine Zahl >= 0");
            } else {
                EL.removeError(tsTBMax);
                prepareCommand();
            }
        }

        private void btOK_Click(object sender, EventArgs e) {
            this.DialogResult = DialogResult.OK;
            if(this.ResultOK_hasSelection != null) this.ResultOK_hasSelection(this, new EventArgs());
        }

        private void tsb_back_Click(object sender, EventArgs e) {
            Int32 max, offset;
            if (Int32.TryParse(tsTBMax.Text, out max) &&
                Int32.TryParse(tsTBOFFSET.Text, out offset)) {
                tsTBOFFSET.Text = Math.Max(0, offset - max).ToString();
                getDataTable();
            }
        }

        private void tsb_forward_Click(object sender, EventArgs e) {
            Int32 max, offset;
            if (Int32.TryParse(tsTBMax.Text, out max) &&
                Int32.TryParse(tsTBOFFSET.Text, out offset)) {
                tsTBOFFSET.Text = (offset + max).ToString();
                getDataTable();
            }
        }

        private void bt_SearchNew_Click(object sender, EventArgs e) {
            if (EL.Count == 0) {
                getDataTable();
            } else {
                MessageBox.Show("Bitte korrekte Angaben eingeben");
            }
        }

        private void btCANCEL_Click(object sender, EventArgs e) {
            AGDO.CancelAllRequest();
            this.DialogResult = DialogResult.Cancel;
        }
    } 
}
